package bdqn.com.cui.dao.impl;

import bdqn.com.cui.dao.Userdao;
import bdqn.com.cui.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Userdaoimpl extends BaseDao implements Userdao {

    @Override
    public int getcount() {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT COUNT(1) FROM `User`";
        try {
            preparedStatement=conn.prepareStatement(sql);
           resultSet= preparedStatement.executeQuery();
           if (resultSet!=null){
            while (resultSet.next()){
                return resultSet.getInt(1);
            }
           }
           return 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }

    @Override
    public List<User> getidserent(int form, int page) {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT u_id,u_name,u_sex,q_quanxian,u_zhanghao,u_paasword,u_type FROM `User` INNER JOIN quanxian ON quanxian.q_id=`User`.u_qx LIMIT ?,?";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setObject(1,form);
            preparedStatement.setObject(2,page);
            resultSet=preparedStatement.executeQuery();
            List<User> list=new ArrayList<>();
            while (resultSet.next()){
                if (resultSet!=null){
                    User user1=new User();
                    user1.setId(resultSet.getInt(1));
                    user1.setName(resultSet.getString(2));
                    user1.setSex(resultSet.getString(3));
                    user1.setQx(resultSet.getString(4));
                    user1.setZhanghao(resultSet.getString(5));
                    user1.setPassword(resultSet.getString(6));
                    user1.setType(resultSet.getInt(7));
                    list.add(user1);
                }
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }

    @Override
    public List<User> idset(String id) {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT u_id,u_name,u_sex,q_quanxian,u_zhanghao,u_paasword,u_type FROM `User` INNER JOIN quanxian ON quanxian.q_id=`User`.u_qx WHERE u_zhanghao=?";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setObject(1,id);
            resultSet=preparedStatement.executeQuery();

            List<User> list=new ArrayList<>();
            while (resultSet.next()){
                if (resultSet!=null){
                    User user1=new User();
                    user1.setId(resultSet.getInt(1));
                    user1.setName(resultSet.getString(2));
                    user1.setSex(resultSet.getString(3));
                    user1.setQx(resultSet.getString(4));
                    user1.setZhanghao(resultSet.getString(5));
                    user1.setPassword(resultSet.getString(6));
                    user1.setType(resultSet.getInt(7));
                    list.add(user1);
                }
            }
            return list ;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }

    @Override
    public int add(User user) {
        String spl="INSERT INTO `User`(u_name,u_sex,u_qx,u_zhanghao,u_paasword,u_type)VALUES(?,?,?,?,?,'2')";
        Object[] obj={user.getName(),user.getSex(),user.getQx(),user.getZhanghao(),user.getPassword()};
        return Crud(spl,obj);
    }

    @Override
    public int delete(String id) {
        String sql="DELETE FROM `User` WHERE u_zhanghao=?";
        Object[] obj= {id};
        return Crud(sql,obj);
    }

    @Override
    public int update(User user) {
        String sql="UPDATE `User` SET u_name=?,u_sex=?,u_qx=?,u_zhanghao=?,u_paasword=?WHERE u_zhanghao=？";
        Object[] obj={user.getName(),user.getSex(),user.getQx(),user.getZhanghao(),user.getPassword(),user.getId()};
        return Crud(sql,obj);
    }

    @Override
    public User denlu(String zhanghao, String password) {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT u_qx FROM `User` where u_zhanghao=? AND u_paasword=? AND u_type='2'";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setObject(1,zhanghao);
            preparedStatement.setObject(2,password);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                if (resultSet!=null){
                    User user1=new User();
                    user1.setQx(resultSet.getString(1));
                    return user1;
                }
            }
            return null;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }

    @Override
    public int Typeupdate(String type, String tj) {
        String sql="UPDATE `User` SET u_type=? WHERE u_zhanghao=?";
        Object[] obj={type,tj};
        return Crud(sql,obj);
    }

    @Override
    public Map getsex() {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        Map map=new HashMap();
        String sql="SELECT u_sex,count(1) FROM `User` group by u_sex";
        try {
            preparedStatement=conn.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                if (resultSet!=null){
                   map.put(resultSet.getString(1),resultSet.getInt(2));
                }
            }
            return map;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }

    @Override
    public int useradd(User user) {
       return 0;
    }

    @Override
    public int zcselent(String zhanghao) {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT * FROM `User` where u_zhanghao=? ";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setObject(1,zhanghao);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                if (resultSet!=null){
                    return 1;
                }
            }
            return 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }


}
